package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblLegalDisputregistration;

import javax.annotation.Resource;
import java.math.BigDecimal;

public class TblLegalDisputregistrationMapperSqlConfig {
    @Resource
    private TblLegalDisputregistrationMapper tblLegalDisputregistrationMapper;

    public String findListByPageInfo(PageInfo<TblLegalDisputregistration> pageInfo, TblLegalDisputregistration dispute, BigDecimal pid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT TLD.DISPUTEID,TLD.DISPUTENO,TCU.CONTRACTNAME,TCU.CONTRACTNO,TLD.DISPUTETYPE,TLD.ATTORNEY,TLD.ISUEGENT,TLD.WHETHERSUED,TLD.LASTDEALDATE,TLN.NEGOTIAID,TLP.PROCEEDID,TLL.LITIGATIONID,TLA.ARBITRAID,TLQ.QUALID,\n" +
                "TLF.INFORID,TLC.CLOSEID,\n" +
                "(SELECT COUNT(0) FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN WHERE DISPUINFO = TLD.DISPUTEID) AS XSCOUNT,\n" +
                "(SELECT COUNT(0) FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN WHERE DISPUINFO = TLD.DISPUTEID) AS SSCOUNT,\n" +
                "(SELECT COUNT(0) FROM TBL_LEGAL_ARBITRATSETTLEMENT WHERE NEGOTIATEINFO IN (SELECT NEGOTIATEINFO FROM TBL_LEGAL_NEGOTIATEDSETTLEMEN WHERE DISPUINFO = TLD.DISPUTEID)) AS ZCCOUNT,\n" +
                "(SELECT COUNT(0) FROM TBL_LEGAL_CLOSEINFORMATION WHERE DISPUTINFO = TLD.DISPUTEID) AS CLOSECOUNT,TLD.DISPUTEITEM \n" +
                "FROM TBL_LEGAL_DISPUTREGISTRATION TLD \n" +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TLD.CONTRACTINFO = TCU.CONTRACTID \n" +
                "LEFT JOIN TBL_STAFF TS ON TLD.DISPUTEUNDERTAKER = TS.STAFFID\n" +
                "LEFT JOIN TBL_LEGAL_NEGOTIATEDSETTLEMEN TLN on TLD.DISPUTEID = TLN.DISPUINFO\n" +
                "LEFT JOIN TBL_LEGAL_LITIGATIONSETTLEMENT TLL ON TLD.DISPUTEID = TLL.DISPUTEINFO\n" +
                "LEFT JOIN TBL_LEGAL_PROCEEDINGSRECORD TLP ON TLL.LITIGATIONID = TLP.LITIGATIONINFO\n" +
                "LEFT JOIN TBL_LEGAL_ARBITRATSETTLEMENT TLA ON TLA.NEGOTIATEINFO = TLN.NEGOTIAID\n" +
                "LEFT JOIN TBL_LEGAL_QUALIFICATION TLQ ON TLQ.DISPUTEINFO = TLD.DISPUTEID\n" +
                "LEFT JOIN TBL_LEGAL_FROZENACCOUNT TLF ON TLP.PROCEEDID = TLF.PROCEEDINFO\n" +
                "LEFT JOIN TBL_LEGAL_CLOSEINFORMATION TLC ON TLC.DISPUTINFO = TLD.DISPUTEID\n" +
                "WHERE tld.LINKORG = " + pid );

        if (dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            sqlSb.append(" AND tld.DISPUTENO LIKE '%" + dispute.getDisputeno() + "%'");
        }
        if (dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            sqlSb.append(" AND tld.DISPUTEITEM LIKE '%" + dispute.getDisputeitem() + "%'");
        }
        if (dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            sqlSb.append(" AND tld.DISPUTETYPE LIKE '%" + dispute.getDisputetype() + "%'");
        }
        if (dispute.getContractname() != null && !"".equals(dispute.getContractname())) {
            sqlSb.append(" AND tld.tcu.CONTRACTNAME LIKE '%" + dispute.getContractname() + "%'");
        }
        if (dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            sqlSb.append(" AND tld.PLAINTIFF LIKE '%" + dispute.getPlaintiff() + "%'");
        }
        if (dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            sqlSb.append(" AND tld.DEFENDANT LIKE '%" + dispute.getDefendant() + "%'");
        }
        if (dispute.getUniqueResult() != null && dispute.getUniqueResult() == 1) {
            String disputeIds = this.tblLegalDisputregistrationMapper.findDisputeIdsForDisputeClose(pid);
            sqlSb.append(" AND tld.disputeId NOT IN (" + disputeIds + ")");
        }

        sqlSb.append(" ORDER BY disputeId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPageInfoCount(TblLegalDisputregistration dispute, BigDecimal pid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) FROM TBL_LEGAL_DISPUTREGISTRATION tld\n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu on tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "LEFT JOIN TBL_STAFF TS ON tld.DISPUTEUNDERTAKER = TS.STAFFID\n" +
                "WHERE tld.LINKORG = " + pid );

        if (dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            sqlSb.append(" AND tld.DISPUTENO LIKE '%" + dispute.getDisputeno() + "%'");
        }
        if (dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            sqlSb.append(" AND tld.DISPUTEITEM LIKE '%" + dispute.getDisputeitem() + "%'");
        }
        if (dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            sqlSb.append(" AND tld.DISPUTETYPE LIKE '%" + dispute.getDisputetype() + "%'");
        }
        if (dispute.getContractname() != null && !"".equals(dispute.getContractname())) {
            sqlSb.append(" AND tld.tcu.CONTRACTNAME LIKE '%" + dispute.getContractname() + "%'");
        }
        if (dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            sqlSb.append(" AND tld.PLAINTIFF LIKE '%" + dispute.getPlaintiff() + "%'");
        }
        if (dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            sqlSb.append(" AND tld.DEFENDANT LIKE '%" + dispute.getDefendant() + "%'");
        }
        if (dispute.getUniqueResult() != null && dispute.getUniqueResult() == 1) {
            String disputeIds = this.tblLegalDisputregistrationMapper.findDisputeIdsForDisputeClose(pid);
            sqlSb.append(" AND disputeId NOT IN (" + disputeIds + ")");
        }

        sqlSb.append(" ORDER BY disputeId DESC");
        String sql = sqlSb.toString();
        return sql;
    }


    public String saveDiputregistration(TblLegalDisputregistration dispute) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_LEGAL_DISPUTREGISTRATION (DISPUTEID");
        StringBuffer value = new StringBuffer(" VALUES (CIRCULATION_SEQUENCE.nextval");

        if(dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            column.append(",DISPUTENO");
            value.append(",'"+dispute.getDisputeno()+"'");
        }
        if(dispute.getDisputestatus() != null && !"".equals(dispute.getDisputestatus())) {
            column.append(",DISPUTESTATUS");
            value.append(",'"+dispute.getDisputestatus()+"'");
        }
        if(dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            column.append(",DISPUTETYPE");
            value.append(",'"+dispute.getDisputetype()+"'");
        }
        if(dispute.getContractinfo() != null && !"".equals(dispute.getContractinfo())) {
            column.append(",CONTRACTINFO");
            value.append(",'"+dispute.getContractinfo()+"'");
        }
        if(dispute.getDisputecours() != null && !"".equals(dispute.getDisputecours())) {
            column.append(",DISPUTECOURS");
            value.append(",'"+dispute.getDisputecours()+"'");
        }
        if(dispute.getIsuegent() != null && !"".equals(dispute.getIsuegent())) {
            column.append(",ISUEGENT");
            value.append(",'"+dispute.getIsuegent()+"'");
        }
        if(dispute.getWhethersued() != null && !"".equals(dispute.getWhethersued())) {
            column.append(",WHETHERSUED");
            value.append(",'"+dispute.getWhethersued()+"'");
        }
        if(dispute.getDisputeundertaker() != null && !"".equals(dispute.getDisputeundertaker())) {
            column.append(",DISPUTEUNDERTAKER");
            value.append(",'"+dispute.getDisputeundertaker()+"'");
        }
        if(dispute.getLastdealdate() != null && !"".equals(dispute.getLastdealdate())) {
            column.append(",LASTDEALDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(dispute.getLastdealdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //value.append(",TO_DATE('"+ DateUtil.parseDate(dispute.getLastdealdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //value.append(",'"+dispute.getLastdealdate()+"'");
        }
        if(dispute.getSolutionsuggestions() != null && !"".equals(dispute.getSolutionsuggestions())) {
            column.append(",SOLUTIONSUGGESTIONS");
            value.append(",'"+dispute.getSolutionsuggestions()+"'");
        }
        if(dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            column.append(",PLAINTIFF");
            value.append(",'"+dispute.getPlaintiff()+"'");
        }
        if(dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            column.append(",DEFENDANT");
            value.append(",'"+dispute.getDefendant()+"'");
        }
        if(dispute.getAttorney() != null && !"".equals(dispute.getAttorney())) {
            column.append(",ATTORNEY");
            value.append(",'"+dispute.getAttorney()+"'");
        }
        if(dispute.getAttorneyphont() != null && !"".equals(dispute.getAttorneyphont())) {
            column.append(",ATTORNEYPHONT");
            value.append(",'"+dispute.getAttorneyphont()+"'");
        }
        if(dispute.getLinkorg() != null && !"".equals(dispute.getLinkorg())) {
            column.append(",LINKORG");
            value.append(",'"+dispute.getLinkorg()+"'");
        }
        if(dispute.getCreatestaff() != null && !"".equals(dispute.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'"+dispute.getCreatestaff()+"'");
        }
        if(dispute.getCreatetime() != null ) {
            column.append(",CREATETIME");
            value.append(",TO_DATE('"+ DateUtil.parseDate(dispute.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
            //value.append(",'"+dispute.getCreatetime()+"'");
        }
        if(dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            column.append(",DISPUTEITEM");
            value.append(",'"+dispute.getDisputeitem()+"'");
        }
        if(dispute.getIsattorney() != null && !"".equals(dispute.getIsattorney())) {
            column.append(",ISATTORNEY");
            value.append(",'"+dispute.getIsattorney()+"'");
        }
        if(dispute.getDispuinfo() != null && !"".equals(dispute.getDispuinfo())) {
            column.append(",DISPUINFO");
            value.append(",'"+dispute.getDispuinfo()+"'");
        }
        if(dispute.getCoordination() != null && !"".equals(dispute.getCoordination())) {
            column.append(",COORDINATION");
            value.append(",'"+dispute.getCoordination()+"'");
        }
        if(dispute.getCasecause() != null && !"".equals(dispute.getCoordination())) {
            column.append(",CASECAUSE");
            value.append(",'"+dispute.getCoordination()+"'");
        }
        if(dispute.getSsproject() != null && !"".equals(dispute.getSsproject())) {
            column.append(",SSPROJECT");
            value.append(",'"+dispute.getSsproject()+"'");
        }
        if(dispute.getCourtfirst() != null && !"".equals(dispute.getCourtfirst())) {
            column.append(",COURTFIRST");
            value.append(",'"+dispute.getCourtfirst()+"'");
        }
        if(dispute.getLitigationamount() != null && !"".equals(dispute.getLitigationamount())) {
            column.append(",LITIGATIONAMOUNT");
            value.append(",'"+dispute.getLitigationamount()+"'");
        }
        if(dispute.getOccurrencetime() != null ) {
            column.append(",OCCURRENCETIME");
            value.append(",TO_DATE('"+ DateUtil.parseDate(dispute.getOccurrencetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(dispute.getSubsidiaries() != null && !"".equals(dispute.getSubsidiaries())) {
            column.append(",SUBSIDIARIES");
            value.append(",'"+dispute.getSubsidiaries()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }


    public String findListByPageInfoDispute(PageInfo<TblLegalDisputregistration> pageInfo,
                                            TblLegalDisputregistration dispute, BigDecimal pid,String disputeIds) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * FROM TBL_LEGAL_DISPUTREGISTRATION TLD\n" +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TLD.CONTRACTINFO = TCU.CONTRACTID\n" +
                "WHERE TLD.LINKORG = "+pid);

        if (dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            sqlSb.append(" AND disputeNo LIKE '%" + dispute.getDisputeno() + "%'");
        }
        if (dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            sqlSb.append(" AND disputeItem LIKE '%" + dispute.getDisputeitem() + "%'");
        }
        if (dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            sqlSb.append(" AND disputeType LIKE '%" + dispute.getDisputetype() + "%'");
        }
        if (dispute.getContractinfo() != null && !"".equals(dispute.getContractinfo())) {
            sqlSb.append(" AND CONTRACTINFO LIKE '%" + dispute.getContractinfo() + "%'");
        }
        if (dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            sqlSb.append(" AND PLAINTIFF LIKE '%" + dispute.getPlaintiff() + "%'");
        }
        if (dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            sqlSb.append(" AND DEFENDANT LIKE '%" + dispute.getDefendant() + "%'");
        }

        if (disputeIds != null){
            sqlSb.append(" AND DISPUTEID NOT IN ("+disputeIds+")");
        }

        sqlSb.append(" ORDER BY disputeId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPageInfoDisputeCount(TblLegalDisputregistration dispute, BigDecimal pid,String disputeIds) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) FROM TBL_LEGAL_DISPUTREGISTRATION TLD\n" +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TLD.CONTRACTINFO = TCU.CONTRACTID\n" +
                "WHERE TLD.LINKORG = "+pid);

        if (dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            sqlSb.append(" AND disputeNo LIKE '%" + dispute.getDisputeno() + "%'");
        }
        if (dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            sqlSb.append(" AND disputeItem LIKE '%" + dispute.getDisputeitem() + "%'");
        }
        if (dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            sqlSb.append(" AND disputeType LIKE '%" + dispute.getDisputetype() + "%'");
        }
        if (dispute.getContractname() != null && !"".equals(dispute.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + dispute.getContractname() + "%'");
        }
        if (dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            sqlSb.append(" AND PLAINTIFF LIKE '%" + dispute.getPlaintiff() + "%'");
        }
        if (dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            sqlSb.append(" AND DEFENDANT LIKE '%" + dispute.getDefendant() + "%'");
        }

        if (disputeIds != null ){
            sqlSb.append(" AND DISPUTEID NOT IN ("+disputeIds+")");
        }

        sqlSb.append(" ORDER BY DISPUTEID DESC");
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByDispute(PageInfo<TblLegalDisputregistration> pageInfo, TblLegalDisputregistration dispute) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * FROM  TBL_LEGAL_DISPUTREGISTRATION tld \n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu on tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "WHERE LINKORG =  " + dispute.getLinkorg());

        if (dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            sqlSb.append(" AND DISPUTENO LIKE '%" + dispute.getDisputeno() + "%'");
        }
        if (dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            sqlSb.append(" AND DISPUTEITEM LIKE '%" + dispute.getDisputeitem() + "%'");
        }
        if (dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            sqlSb.append(" AND DISPUTETYPE LIKE '%" + dispute.getDisputetype() + "%'");
        }
        if (dispute.getDispuinfo() != null && !"".equals(dispute.getDispuinfo())) {
            sqlSb.append(" AND CONTRACTINFO LIKE '%" + dispute.getDispuinfo() + "%'");
        }
        if (dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            sqlSb.append(" AND PLAINTIFF LIKE '%" + dispute.getPlaintiff() + "%'");
        }
        if (dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            sqlSb.append(" AND DEFENDANT LIKE '%" + dispute.getDefendant() + "%'");
        }
        if (dispute.getDisputeid() != null && dispute.getDisputeid() == 1) {
            String disputeIds = this.tblLegalDisputregistrationMapper.findDisputeIdsForDisputeClose(dispute.getLinkorg());
            sqlSb.append(" AND disputeId NOT IN (" + disputeIds + ")");
        }

        sqlSb.append(" ORDER BY disputeId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String findListByDisputeCount(TblLegalDisputregistration dispute) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM  TBL_LEGAL_DISPUTREGISTRATION tld \n" +
                "LEFT JOIN TBL_CYHW_UNIT tcu on tld.CONTRACTINFO = tcu.CONTRACTID\n" +
                "WHERE LINKORG =  " + dispute.getLinkorg());

        if (dispute.getDisputeno() != null && !"".equals(dispute.getDisputeno())) {
            sqlSb.append(" AND DISPUTENO LIKE '%" + dispute.getDisputeno() + "%'");
        }
        if (dispute.getDisputeitem() != null && !"".equals(dispute.getDisputeitem())) {
            sqlSb.append(" AND DISPUTEITEM LIKE '%" + dispute.getDisputeitem() + "%'");
        }
        if (dispute.getDisputetype() != null && !"".equals(dispute.getDisputetype())) {
            sqlSb.append(" AND DISPUTETYPE LIKE '%" + dispute.getDisputetype() + "%'");
        }
        if (dispute.getDispuinfo() != null && !"".equals(dispute.getDispuinfo())) {
            sqlSb.append(" AND CONTRACTINFO LIKE '%" + dispute.getDispuinfo() + "%'");
        }
        if (dispute.getPlaintiff() != null && !"".equals(dispute.getPlaintiff())) {
            sqlSb.append(" AND PLAINTIFF LIKE '%" + dispute.getPlaintiff() + "%'");
        }
        if (dispute.getDefendant() != null && !"".equals(dispute.getDefendant())) {
            sqlSb.append(" AND DEFENDANT LIKE '%" + dispute.getDefendant() + "%'");
        }
        if (dispute.getDisputeid() != null && dispute.getDisputeid() == 1) {
            String disputeIds = this.tblLegalDisputregistrationMapper.findDisputeIdsForDisputeClose(dispute.getLinkorg());
            sqlSb.append(" AND disputeId NOT IN (" + disputeIds + ")");
        }

        sqlSb.append(" ORDER BY disputeId DESC");
        String sql = sqlSb.toString();
        return sql;
    }


    public String updateDiputregistration(TblLegalDisputregistration stration) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_LEGAL_DISPUTREGISTRATION SET DISPUTEITEM = '"+stration.getDisputeitem()+"'");
        if(stration.getDisputeno() != null && !"".equals(stration.getDisputeno())) {
            sql.append(" , DISPUTENO = '"+stration.getDisputeno()+"'");
        }
        if(stration.getDisputestatus() != null && !"".equals(stration.getDisputestatus())) {
            sql.append(" , DISPUTESTATUS = '"+stration.getDisputestatus()+"'");
        }
        if(stration.getDisputetype() != null && !"".equals(stration.getDisputetype())) {
            sql.append(" , DISPUTETYPE = '"+stration.getDisputetype()+"'");
        }
        if(stration.getContractinfo() != null && !"".equals(stration.getContractinfo())) {
            sql.append(" , CONTRACTINFO = '"+stration.getContractinfo()+"'");
        }
        if(stration.getDisputecours() != null && !"".equals(stration.getDisputecours())) {
            sql.append(" , DISPUTECOURS = '"+stration.getDisputecours()+"'");
        }
        if(stration.getIsuegent() != null && !"".equals(stration.getIsuegent())) {
            sql.append(" , ISUEGENT = '"+stration.getIsuegent()+"'");
        }
        if(stration.getWhethersued() != null && !"".equals(stration.getWhethersued())) {
            sql.append(" , WHETHERSUED = '"+stration.getWhethersued()+"'");
        }
        if(stration.getDisputeundertaker() != null && !"".equals(stration.getDisputeundertaker())) {
            sql.append(" , DISPUTEUNDERTAKER = '"+stration.getDisputeundertaker()+"'");
        }
        if(stration.getLastdealdate() != null && !"".equals(stration.getLastdealdate())) {
            sql.append(" ,LASTDEALDATE = TO_DATE('"+DateUtil.parseDate(stration.getLastdealdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");

        }
        if(stration.getSolutionsuggestions() != null && !"".equals(stration.getSolutionsuggestions())) {
            sql.append(" , SOLUTIONSUGGESTIONS = '"+stration.getSolutionsuggestions()+"'");
        }
        if(stration.getPlaintiff() != null && !"".equals(stration.getPlaintiff())) {
            sql.append(" , PLAINTIFF = '"+stration.getPlaintiff()+"'");
        }
        if(stration.getDefendant() != null && !"".equals(stration.getDefendant())) {
            sql.append(" , DEFENDANT = '"+stration.getDefendant()+"'");
        }
        if(stration.getAttorney() != null && !"".equals(stration.getAttorney())) {
            sql.append(" , ATTORNEY = '"+stration.getAttorney()+"'");
        }
        if(stration.getAttorneyphont() != null && !"".equals(stration.getAttorneyphont())) {
            sql.append(" , ATTORNEYPHONT = '"+stration.getAttorneyphont()+"'");
        }

        if(stration.getLinkorg() != null && !"".equals(stration.getLinkorg())) {
            sql.append(" , LINKORG = '"+stration.getLinkorg()+"'");
        }

        if(stration.getDispuinfo() != null && !"".equals(stration.getDispuinfo())) {
            sql.append(" , DISPUINFO = '"+stration.getDispuinfo()+"'");
        }

        if(stration.getCreatestaff() != null && !"".equals(stration.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+stration.getCreatestaff()+"'");
        }

        if(stration.getCreatetime() != null && !"".equals(stration.getCreatetime())) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(stration.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }

        if(stration.getIsattorney() != null && !"".equals(stration.getIsattorney())) {
            sql.append(" , ISATTORNEY = '"+stration.getIsattorney()+"'");
        }
        if(stration.getCoordination() != null && !"".equals(stration.getCoordination())) {
            sql.append(" , COORDINATION = '"+stration.getCoordination()+"'");
        }
        if(stration.getCasecause() != null && !"".equals(stration.getCasecause())) {
            sql.append(" , CASECAUSE = '"+stration.getCasecause()+"'");
        }
        if(stration.getSsproject() != null && !"".equals(stration.getSsproject())) {
            sql.append(" , SSPROJECT = '"+stration.getSsproject()+"'");
        }
        if(stration.getCourtfirst() != null && !"".equals(stration.getCourtfirst())) {
            sql.append(" , COURTFIRST = '"+stration.getCourtfirst()+"'");
        }
        if(stration.getLitigationamount() != null && !"".equals(stration.getLitigationamount())) {
            sql.append(" , LITIGATIONAMOUNT = '"+stration.getLitigationamount()+"'");
        }
        if(stration.getOccurrencetime() != null && !"".equals(stration.getOccurrencetime())) {
            sql.append(" ,OCCURRENCETIME = TO_DATE('"+DateUtil.parseDate(stration.getOccurrencetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(stration.getSubsidiaries() != null && !"".equals(stration.getSubsidiaries())) {
            sql.append(" , SUBSIDIARIES = '"+stration.getSubsidiaries()+"'");
        }

        sql.append(" WHERE DISPUTEID = '"+stration.getDisputeid()+"'");
        return sql.toString();
    }

    public String saveAttacheMent(Integer type, Integer bid, BigDecimal aid){
        String tableName = "";
        String bidColName = "";
        String aidColName = "";
        String attType = null;
        switch (type){
            case 1:
                tableName = "TBL_LEGAL_DISPUTE_ATT";
                bidColName = "DISPUTEID";
                aidColName = "ATTID";
                break;
            case 2:
                tableName = "TBL_LEGAL_NEGOTIATEE_ATT";
                bidColName = "NEGOTIAID";
                aidColName = "ATTID";
                break;
            case 3:
                tableName = "TBL_LEGAL_LSETTLEMENT_ATT";
                bidColName = "LITIGATIONID";
                aidColName = "ATTID";
                break;
            case 4:
                tableName = "TBL_LEGAL_ARBITRATION_ATT";
                bidColName = "ARBITRAID";
                aidColName = "ATTID";
                break;
            case 5:
                tableName = "TBL_LEGAL_CONTRACT_PROJECT_ATT";
                bidColName = "PROJECTID";
                aidColName = "ATTID";
                break;
            case 6:
                tableName = "TBL_CONTRACTNODE_ATT";
                bidColName = "NODEID";
                aidColName = "ATTID";
                attType = "ATTTYPE";
                break;  
                
                
        }
        StringBuilder sql = new StringBuilder("INSERT INTO ")
                .append(tableName)
                .append(" (")
                .append(aidColName)
                .append(",")
                .append(bidColName);
	       if(attType != null) {
	    	   sql .append(","+attType);
	       }    
	            
	       sql.append(") VALUES(")
	                .append(aid)
	                .append(",")
	                .append(bid);
	       if(attType != null) {
	            sql.append(",1");
	      }  
	       sql .append(")");
        return sql.toString();
    }
    //根据附件id,删除单个
    public String deleteAttacheMentByBid(Integer type, Integer aid){
        String tableName = "";
        String aidColName = "";

        switch (type){
            case 1:
                tableName = "TBL_LEGAL_DISPUTE_ATT";
                aidColName = "ATTID";
                break;
            case 2:
                tableName = "TBL_LEGAL_NEGOTIATEE_ATT";
                aidColName = "ATTID";
                break;
            case 3:
                tableName = "TBL_LEGAL_LSETTLEMENT_ATT";
                aidColName = "ATTID";
                break;
            case 4:
                tableName = "TBL_LEGAL_ARBITRATION_ATT";
                aidColName = "ATTID";
                break;
            case 5:
                tableName = "TBL_LEGAL_CONTRACT_PROJECT_ATT";
                aidColName = "ATTID";
                break;
                
                
        }
        StringBuilder sql = new StringBuilder("DELETE FROM ")
                .append(tableName)
                .append(" WHERE ATTID = ")
                .append(aid);
        return sql.toString();
    }
    //根据业务ID，批量删除
    public String deleteAttacheMents(Integer type, BigDecimal bid){
        String tableName = "";
        String aidColName = "";

        switch (type){
            case 1:
                tableName = "TBL_LEGAL_DISPUTE_ATT";
                aidColName = "DISPUTEID";
                break;
            case 2:
                tableName = "TBL_LEGAL_NEGOTIATEE_ATT";
                aidColName = "NEGOTIAID";
                break;
            case 3:
                tableName = "TBL_LEGAL_LSETTLEMENT_ATT";
                aidColName = "LITIGATIONID";
                break;
            case 4:
                tableName = "TBL_LEGAL_ARBITRATION_ATT";
                aidColName = "ARBITRAID";
                break;
            case 5:
                tableName = "TBL_LEGAL_CONTRACT_PROJECT_ATT";
                aidColName = "PROJECTID";
                break;
                
        }
        StringBuilder sql = new StringBuilder("DELETE FROM ")
                .append(tableName)
                .append(" WHERE ")
                .append(aidColName)
                .append(" = ")
                .append(bid);
        return sql.toString();
    }
    //回显
    public String findAttacheMentByBid(Integer type, Integer bid){
        String tableName = "";
        String bidColName = "";

        switch (type){
            case 1:
                tableName = "TBL_LEGAL_DISPUTE_ATT";
                bidColName = "DISPUTEID";
                break;
            case 2:
                tableName = "TBL_LEGAL_NEGOTIATEE_ATT";
                bidColName = "NEGOTIAID";
                break;
            case 3:
                tableName = "TBL_LEGAL_LSETTLEMENT_ATT";
                bidColName = "LITIGATIONID";
                break;
            case 4:
                tableName = "TBL_LEGAL_ARBITRATION_ATT";
                bidColName = "ARBITRAID";
                break;
            case 5:
                tableName = "TBL_LEGAL_CONTRACT_PROJECT_ATT";
                bidColName = "PROJECTID";
                break;
                
        }
        StringBuilder sql = new StringBuilder("SELECT TA.* FROM ")
                .append(tableName)
                .append(" TLD LEFT JOIN TBL_ATTACHMENT TA ON TLD.ATTID = TA.ATTID WHERE TLD.")
                .append(bidColName)
                .append(" = ")
                .append(bid);
        return sql.toString();
    }



    public String findDisputeIdsForDispute(String colName, String tableName, String orgColName, String whSql, BigDecimal oid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT "+colName+" FROM "+tableName+ " WHERE " + orgColName +" = " + oid + whSql);


        //sqlSb.append(" ORDER BY disputeId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }
}
